<?php
header("Content-type: text/html; charset=utf-8");
class Aio_model extends CI_Model{
	private $sqlite;
	function __construct(){
        parent::__construct();
		//连接sqlite3数据库
		$this->sqlite = $this->load->database('pdo',true);
    }
	//大类
	function cat_view(){
		$res = $this->sqlite->query("select * from categories order by id desc");
		return $res->result_array();
	}
	
	function new_cat(){
		$name = $this->input->post('name');
		$rank = $this->input->post('rank');
		$this->sqlite->query("insert into categories values(null,'{$rank}','{$name}')");
		echo "<script language=javascript>
				alert('新建成功！');
				window.location='".base_url()."aio_cat';
				</script>";
	}
	function cat_file_view($id){
		$sql = "select * from categories where id='{$id}'";
		$res = $this->sqlite->query($sql);
		return $res->row_array();
	}
	function modify_cat(){
		$id = $this->input->post('id');
		$rank = $this->input->post('rank');
		$name = $this->input->post('name');
		$this->sqlite->query("update categories set rank='{$rank}',name='{$name}' where id='{$id}'");
		echo "<script language=javascript>
				alert('修改成功！');
				window.location='".base_url()."aio_cat';
				</script>";
	}
	function del_cat($id){
		$sql1 = "delete from categories where id='{$id}'";
		$sql2 =	"delete from subcategories where category_id='{$id}'";
		$this->sqlite->query($sql1);
		$this->sqlite->query($sql2);
		echo "<script language=javascript>
				alert('删除成功！');
				window.location='".base_url()."aio_cat';
				</script>";
	}
	
	//子类
	function subcat_view($id){
		$res = $this->sqlite->query("select * from subcategories where category_id='{$id}'");
		return $res->result_array();
	}
	function new_subcat(){
		$cid = $this->input->post('category');
		$name = $this->input->post('name');
		$rank = $this->input->post('rank');
		$this->sqlite->query("insert into subcategories values(null,'{$rank}','{$name}','{$cid}')");
		echo "<script language=javascript>
				alert('新建成功！');
				window.location='".base_url()."aio_subcat';
				</script>";
	}
	function subcat_file_view($id){
		$sql = "select * from subcategories where id='{$id}'";
		$res = $this->sqlite->query($sql);
		return $res->row_array();
	}
	function modify_subcat(){
		$id = $this->input->post('id');
		$rank = $this->input->post('rank');
		$name = $this->input->post('name');
		$cid = $this->input->post('category');
		$this->sqlite->query("update subcategories set rank='{$rank}',name='{$name}',category_id='{$cid}' where id='{$id}'");
		echo "<script language=javascript>
				alert('修改成功！');
				window.location='".base_url()."aio_subcat';
				</script>";
	}
	function del_subcat($id){
		$this->sqlite->query("delete from subcategories where id='{$id}'");
		echo "<script language=javascript>
				alert('删除成功！');
				window.location='".base_url()."aio_subcat';
				</script>";
	}
	
	//绘本
	function books_view($sql){
		$res = $this->sqlite->query($sql);
		foreach($res->result_array() as $row){
			$sql = "select 
					b.name sname,c.name cname 
				from 
					books_subcategories a left join subcategories b 
				on 
					a.sid=b.id left join categories c 
				on
					b.category_id=c.id
				where a.bid='{$row['id']}'";
			$c_name = array();
			$s_name = array();
			$rs = $this->sqlite->query($sql);
			foreach($rs->result_array() as $r){
				$s_name[] = $r['sname'];
				$c_name[] = $r['cname'];
			}
			$category = implode(',',array_unique($c_name));//由于一个大类对应多个子类，使得连接表后的大类名字重复
			$subcategory = implode(',',$s_name);
			$arr[]=array(
				'id'=>$row['id'],
				'name'=>$row['title'],
				'category'=>$category,
				'subcategory'=>$subcategory
			);
		}
		return $arr;
	}
	function book_file_view($id){
		$sql = "select 
					a.id,a.title name,a.status,b.sid sid
				from books a left join books_subcategories b 
					on a.id=b.bid 
				where a.id='{$id}'"; 
		$res = $this->sqlite->query($sql);
		foreach($res->result_array() as $row){
			$name[] = $row['name'];
			$status[] = $row['status'];
			$sid[] = $row['sid'];
		}
		//多选框
		$res = $this->sqlite->query("select id,name from categories order by id desc");
		$option = '';
		foreach($res->result_array() as $r){
			$option .= "<option value =".$r['name']." disabled>——>".$r['name']."<——</option>";
			$rs = $this->sqlite->query("select id,name from subcategories where category_id='{$r['id']}'");
			foreach($rs->result_array() as $rows){
				$option .= "<option value ='".$rows['id']."' ".(in_array($rows['id'],$sid)?'selected':'').">-".$rows['name']."</option>";
			}
		}
		return array(
			'id'=>$id,
			'name'=>$name[0],
			'status'=>$status[0],
			'options'=>$option
		);
	}
	function modify_book(){
		$bid = $this->input->post('id');
		$sid = $this->input->post('subcategory');
		$status = $this->input->post('status');
		$name = $this->input->post('name');
		$this->sqlite->query("update books set status='{$status}' where id='{$bid}'");
		$this->sqlite->query("delete from books_subcategories where bid='{$bid}'");
		for($i=0;$i<count($sid);$i++){
			$this->sqlite->query("insert into books_subcategories values (null,'{$sid[$i]}','{$bid}')");	
		}
		echo "<script language=javascript>
				alert('修改成功！');
				window.location='".base_url()."aio_books';
				</script>";	
	}
	
	//分页
	function page($sql,$page='',$pageSize = 20, $indexLength = 5) {//$sql,原始sql语句，$pageSize,每页显示记录数，$indexLength，显示页码 
	global $pageIndexBar;//$db,mysql new来的,$pageIndexBar直接当前页面用的导航条
	//得到当前的URL值 
	$curUrl = $_SERVER['QUERY_STRING']; 
	if($curUrl == '') { 
	  $curUrl = "?page="; 
	} else { 
	  $curUrl = preg_replace("/&?page=\d+/", '', $curUrl); 
	  $curUrl = "?" . $curUrl . "&page="; 
	} 
	//查得总记录数 
	$rs = $this->sqlite->query($sql);//CI中的DB类 
	$totalNum = $rs->num_rows(); 
	//取得最大页数 
	$maxPage = ceil($totalNum / $pageSize); 
	$maxPage = $maxPage < 1 ? 1 : $maxPage; 
	//取得当前页码 
	$curPage = $page; 
	$curPage = ($curPage == '' || $curPage < 1) ? 1 : $curPage; 
	if($curPage > $maxPage) { 
	  $curPage = $maxPage; 
	} 
	//分页导航 
	if($maxPage <= $indexLength) { 
	  $firstNum = 1; 
	  $lastNum = $maxPage; 
	} else { 
	  $offset = floor($indexLength / 2); 
	  if(($curPage - $offset) <= 1) { 
	   $firstNum = 1; 
	  } elseif(($curPage + $offset) > $maxPage) { 
	   $firstNum = $maxPage - $indexLength + 1; 
	  } else { 
	   $firstNum = $curPage - $offset; 
	  } 
	  $lastNum = $firstNum + $indexLength - 1; 
	} 
	$pageIndex='';
	for($i = $firstNum; $i <= $lastNum; $i++) { 
	  if($curPage == $i) { 
	   $pageIndex .= "<a class='number current' href='javascript:void(0)'>".$i."</a>"; 
	  } else { 
	   $pageIndex .= "<a class='number' href='" . $curUrl . $i . "'>" . $i . "</a>  "; 
	  } 
	} 
	$pageIndexTotal = "共<span class='fred'>$maxPage</span>页  <span class='fred'>$totalNum</span>条记录  "; 
	$pageIndexNav = $curPage > 1 ? "<a href='" . $curUrl . "1'>&laquo; 首页</a>  <a href='" . $curUrl . ($curPage - 1) . "'>&laquo; 上一页</a>  " : "首页  上一页  "; 
	$pageIndexEnd = $curPage < $maxPage ? "<a href='" . $curUrl . ($curPage + 1) . "'>下一页 &raquo;</a>  <a href='" . $curUrl . $maxPage . "'>末页 &raquo;</a>" : "下一页  末页"; 
	if($maxPage == 1) { 
	  $pageIndexBar = $pageIndexTotal; 
	} else { 
	  $pageIndexBar = $pageIndexTotal . $pageIndexNav . $pageIndex . $pageIndexEnd; 
	} 

	//数据库分页开始 
	$sql = $sql . " LIMIT " . ($curPage - 1) * $pageSize . "," . $pageSize;
	return $sql;//返回sql
	} 
}
?>